R Training: 05 - Case Study Solutions

1 Question 1 (Loading in Data)

Load the 60k version of Ontario Inforce Auto that can be found in the subfolders “S:/Pricing/Data” (in fst format).

The filename is Inf_a_cw_20181031_CTR_ON_60K.fst. Note that CTR (Code Transform) has been run on the builder data so that it’s ready to be used for various pricing purposes.

INFORCE.PATH <- "S:/Pricing/Data/Inforce/"
INFORCE.AUTO.DATA <-  read_fst(paste0(INFORCE.PATH,"Inf_a_cw_20181031_CTR_ON_60K.fst"))

2 Question 2 (Data Manipulation)

Subset your data to only include policies that have a new vehicle.

DATA.Q2 <- INFORCE.AUTO.DATA %>% 
  filter(Veh_Age_Nb <= 0) 

Note here the original unsubsetted data is will be unchanged and the subsetted data is called DATA.Q2. To confirm that the data has been subsetted correctly one can check the levels of the variables.

print(unique(DATA.Q2$Veh_Age_Nb))

[1]  0 -1 -2

3 Question 3 (Data Manipulation)

Get a table of all principal drivers that have been holding a license for more than 5 years and less than 10 years. Keep only the policy number, vehicle ID number, driver ID number, effective date of the policy, number of years the driver has been licensed, gender and the driver total premium term amount.

DATA.Q3 <- INFORCE.AUTO.DATA %>% 
  select(Pol_Policy_No,
         Veh_Id_No,
         Dri_Id_No,
         Pol_Eff_Dt,
         Dri_Type_Cd,
         Dri_Yrs_Licensed_AU_Nb,
         Prm_Trm_Dri_Tot_Am,
         Dri_Gender_Cd) %>% 
  filter(Dri_Type_Cd == "P" &
           Dri_Yrs_Licensed_AU_Nb > 5 &
           Dri_Yrs_Licensed_AU_Nb < 10)

Calculate the average premium by years licensed and by gender.

x <- DATA.Q3 %>% 
  group_by(Dri_Gender_Cd, Dri_Yrs_Licensed_AU_Nb) %>% 
  summarise(Avg_Prm = mean(Prm_Trm_Dri_Tot_Am)) 

4 Question 4 (Data Pre-processing)

Create a new variable for analysis purposes. Using the home main location variable as a proxy, create a new variable that is an indicator of whether or not someone this auto client is a multiline client (has auto and home) or not.

Note: This is not a perfect indicator, since sometimes the home info is not entered but we will use this as a proxy for this exercise.

DATA.Q4 <- INFORCE.AUTO.DATA %>% 
  mutate(multiline = ifelse(Clt_Home_Main_Location_Cd == "", 0, 1))

5 Question 5 (Graphing)

Plot the average driver level premium by vehicle age by gender. Include a bar graph for exposure.

First, create a table with average driver level premium by vehicle age by gender along with exposure counts on a driver level.

Second, plot the average driver level premium by vehicle age by gender. Include a bar graph for exposure.

DATA.Q5 <- ungroup(DATA.Q5)

p <- plot_ly(DATA.Q5, color =~Dri_Gender_Cd, colors = c("red","darkgreen")) %>% 
  add_lines(x = ~Veh_Age_Nb,
            y = ~Avg_Prm) %>% 
  add_bars(x = ~Veh_Age_Nb,
           y = ~Exposures,
           yaxis = "y2",
           opacity = 0.75) %>%
  layout(title = "Average Premium by Vehicle Age by Gender",
         xaxis = list(title = "Vehicle Age", range = c(-2, 25)),
         yaxis = list(title = "Average Premium", side = "left", overlaying = "y2"),
         barmode = "stack",
         yaxis2 = list(title = "Exposure", side = "right"))

6 Question 6 (Functions) - Intermediate to Advanced Level

Write a function to plot any numeric average (on the y axis) by two variables (1 numeric and 1 categorical).

PLOT.GRAPH <- function(DATA,
                       BY.VAR1, # numeric 
                       BY.VAR2, # categorical
                       AVG,
                       COLOR.GRAPH,
                       XAXIS,
                       YAXIS){
  
  df <- DATA[,c(BY.VAR1, BY.VAR2, AVG)]
  names(df) <- c("BY.VAR1", "BY.VAR2","AVG")
  
  DATA.PLOT <- df %>%
    group_by(BY.VAR1, BY.VAR2) %>% 
    summarise(Avg_Prm = mean(AVG, na.rm = TRUE),
              Exposures = sum(n()))
  
  DATA.PLOT <- ungroup(DATA.PLOT)
  
  p <- plot_ly(DATA.PLOT, 
               color =~BY.VAR2, 
               colors = COLOR.GRAPH) %>% 
    add_lines(x = ~BY.VAR1,
              y = ~Avg_Prm) %>% 
    add_bars(x = ~BY.VAR1,
             y = ~Exposures,
             yaxis = "y2",
             opacity = 0.75) %>%
    layout(title = "Average by Variables",
           xaxis = list(title = XAXIS),
           yaxis = list(title = YAXIS, side = "left", overlaying = "y2"),
           barmode = "stack",
           yaxis2 = list(title = "Exposure", side = "right"))
  
  return (p)
}